﻿using ApiLib.AppStart;
using ApiLib.Common;
using ApiLib.Utils;
using SqlSugar;

namespace ApiLib.Models;

/// <summary>
/// 自定义表
///</summary>
[Tenant(GlobalConstants.MainDbConfigId), SugarTable("sys_table_info")]
public class SysTableInfo
{
    /// <summary>
    ///  
    ///</summary>
    [SugarColumn(ColumnName = "id", IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }

    /// <summary>
    /// 表名 
    ///</summary>
    [SugarColumn(ColumnName = "table_name")]
    public string TableName { get; set; } = "";

    /// <summary>
    /// 中文表名 
    ///</summary>
    [SugarColumn(ColumnName = "table_name_cn")]
    public string TableNameCn { get; set; } = "";

    /// <summary>
    /// 表类型 
    ///</summary>
    [SugarColumn(ColumnName = "table_type")]
    public string TableType { get; set; } = "";

    /// <summary>
    /// 表图标 
    ///</summary>
    [SugarColumn(ColumnName = "table_icon")]
    public string TableIcon { get; set; } = "";

    /// <summary>
    /// 表描述 
    ///</summary>
    [SugarColumn(ColumnName = "table_explain")]
    public string TableExplain { get; set; } = "";

    /// <summary>
    /// 是否文件夹 
    ///</summary>
    [SugarColumn(ColumnName = "is_folder")]
    public bool IsFolder { get; set; }

    /// <summary>
    /// 父id 
    ///</summary>
    [SugarColumn(ColumnName = "parent_id")]
    public int ParentId { get; set; }

    /// <summary>
    /// 创建人 
    ///</summary>
    [SugarColumn(ColumnName = "create_user")]
    public string CreateUser { get; set; } = "";

    /// <summary>
    /// 创建时间 
    ///</summary>
    [SugarColumn(ColumnName = "create_time")]
    public DateTime CreateTime { get; set; }


    /// <summary>
    /// 子表
    /// </summary>
    [SugarColumn(IsIgnore = true)]
    public List<SysTableInfo>? Children { get; set; }

    /// <summary>
    /// 字段
    /// </summary>
    [SugarColumn(IsIgnore = true)]
    public List<SysTableFieldInfo>? FieldInfos { get; set; }

    /// <summary>
    /// 表格同步到数据库
    /// </summary>
    /// <param name="db"></param>
    /// <exception cref="MyActionException"></exception>
    public void DbTableSync(ISqlSugarClient db)
    {
        db.Ado.BeginTran();
        try
        {
            if (Id == 0)
            {
                DbTableCreate(db);
            }
            else
            {
                DbTableUpdate(db);
            }
            db.Ado.CommitTran();
        }
        catch (Exception e)
        {
            db.Ado.RollbackTran();
            e.Message.LogError();
            throw new MyActionException("出现异常");
        }
    }
    
    /// <summary>
    /// 获取表名
    /// </summary>
    /// <returns></returns>
    private string GetDbTableName()
    {
        return TableType == "System" ? TableName : "table_" + Id;
    }

    private void DbTableUpdate(ISqlSugarClient db)
    {
        var first = db.Queryable<SysTableInfo>().First(info => info.Id == Id);
        if (first == null)
        {
            throw new MyActionException("不存在");
        }
        db.Updateable(this).ExecuteCommand();
        
        if (IsFolder || FieldInfos == null || FieldInfos.Count == 0)
        {
            return;
        }

        foreach (var fieldInfo in FieldInfos)
        {
            fieldInfo.TableId = Id;
        }

        var fieldInfos = db.Queryable<SysTableFieldInfo>()
            .Where(fieldInfo => fieldInfo.TableId == Id)
            .ToList();
        var tableName = GetDbTableName();
        var sql = "";
        foreach (var fieldInfo in fieldInfos) //原有的
        {
            //新的
            var find = FieldInfos.Find(info => info.Id == fieldInfo.Id);
            if (find == null)
            {
                //删除字段
                db.Deleteable(fieldInfo).ExecuteCommand();
                sql += $"alter table {tableName} drop column {fieldInfo.FieldName};";
            }
            else if (!CommonUtil.Equals(fieldInfo, find, "Id"))
            {
                //修改
                db.Updateable(find).ExecuteCommand();
                if (find.FieldName != fieldInfo.FieldName || find.FieldType != fieldInfo.FieldType ||
                    find.FieldExplain != fieldInfo.FieldExplain || find.NotNull != fieldInfo.NotNull)
                {
                    var fieldSql = GetFieldSql(find);
                    sql += $"alter table {tableName} change {fieldInfo.FieldName} {fieldSql};";
                }
            }
        }

        foreach (var fieldInfo in FieldInfos)
        {
            if (fieldInfo.Id == 0)
            {
                //新增
                db.Insertable(fieldInfo).ExecuteCommand();
                var fieldSql = GetFieldSql(fieldInfo);
                sql += $"alter table {tableName} add {fieldSql};";
            }
        }

        if (!string.IsNullOrEmpty(sql))
        {
            db.Ado.ExecuteCommand(sql);
        }
    }

    private void DbTableCreate(ISqlSugarClient db)
    {
        Id = db.Insertable(this).ExecuteReturnIdentity();
        if (IsFolder || FieldInfos == null || FieldInfos.Count == 0)
        {
            return;
        }

        foreach (var fieldInfo in FieldInfos)
        {
            fieldInfo.Id = 0;
            fieldInfo.TableId = Id;
        }

        var tableName = GetDbTableName();
        var sql = "`id` int(11) NOT NULL AUTO_INCREMENT,";
        foreach (var fieldInfo in FieldInfos)
        {
            sql += GetFieldSql(fieldInfo)+",";
        }

        sql += $"constraint {tableName}_pk primary key (id)";
        sql = $"create table `{tableName}` ({sql}) ENGINE = InnoDB AUTO_INCREMENT = 1 " +
               $"CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci " +
               $"COMMENT = '{TableExplain}' ROW_FORMAT = Compact;";
        db.Insertable(FieldInfos).ExecuteCommand();
        db.Ado.ExecuteCommand(sql);
    }

    private string GetFieldSql(SysTableFieldInfo info)
    {
        var typeName = GetFieldType(info.FieldType);
        var nullStr = info.NotNull ? "NOT NULL" : "DEFAULT NULL";
        var comment = string.IsNullOrEmpty(info.FieldExplain) ? "" : $"COMMENT `{info.FieldExplain}`";
        var sql = $"`{info.FieldName}` {typeName} {nullStr} {comment}";
        return sql;
    }

    private string GetFieldType(TableFieldType type)
    {
        var charset = "CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci";
        return type switch
        {
            TableFieldType.InputNumber => "int(11)",
            TableFieldType.InputDouble => "double(11, 2)",
            TableFieldType.TextArea or TableFieldType.Location => "varchar(500) " + charset,
            TableFieldType.DateTime => "datetime",
            _ => "varchar(255) " + charset
        };
    }
}